SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


CREATE PROCEDURE dbo.[DnnProduct_CDS_AssetsCountAll]
	@DistributorID varchar(50),
	@CategoryID int,
	@WhereClause nvarchar(1000)
AS
BEGIN
	DECLARE @SQLString NVARCHAR(1000)
	DECLARE @ParmDefinition NVARCHAR(100)

	IF (@CategoryID = 0)
	BEGIN
		
		SET @SQLString = N'SELECT count([ID]) FROM DnnProduct_CDS_Assets WHERE 1=1'
		IF (LEN(@DistributorID) > 0) BEGIN SET @SQLString = @SQLString + N' AND DistributorID = @DistributorID' END
	END
	ELSE
	BEGIN
		SET @SQLString = N'WITH CTE (ID, ParentID)
		AS
		(
			SELECT ID, ParentID FROM [DnnProduct_CDS_Categories]
			WHERE ID = @CategoryID
			UNION ALL
			SELECT c.ID, c.ParentID FROM [DnnProduct_CDS_Categories] c
			JOIN CTE ON CTE.ID = c.ParentID
		)
		SELECT count(A.[ID])
		FROM DnnProduct_CDS_Assets A
		JOIN CTE ON A.CategoryID = CTE.ID WHERE 1=1'
		IF (LEN(@DistributorID) > 0) BEGIN SET @SQLString = @SQLString + N' AND DistributorID = @DistributorID' END
	END

	IF (LEN(@WhereClause) > 0)
	BEGIN
		SET @SQLString = @SQLString + N' AND ' + @WhereClause
	END
	
	SET @ParmDefinition = N'@DistributorID varchar(50), @CategoryID int'
	EXECUTE sp_executesql @SQLString, @ParmDefinition, @DistributorID, @CategoryID
END
GO
